package com.lin.sqltable.generator;
 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.SqlRunner;
import cn.hutool.db.ds.DSFactory;
import cn.hutool.setting.Setting;

import com.alibaba.fastjson.JSONArray;
import com.lin.sqltable.entry.Sys_Menu;
import com.lin.sqltable.entry.TableDescription;
import com.lin.sqltable.entry.TableFieldDescription;
 
/**
 * 
 * <p>Description: 获取数据库基本信息的工具类</p>
 * 
 * @author qxl
 * @date 2016年7月22日 下午1:00:34
 */
public class DbInfoUtil {
	private static Setting setting = new Setting("db.setting");
	public static String DRIVER = setting.get("driver");
	public static String USER =  setting.get("user");
	public static String PWD = setting.get("pass");
	public static String URL = setting.get("url");
	/**
	 * 根据数据库的连接参数，获取指定表的基本信息：字段名、字段类型、字段注释
	 * @author linfei
	 * @param table 表名（不指定则加入所有表信息）
	 * @return
	 */
	public static List<Map<String,String>> addTableInfo(String table){
		DataSource ds = DSFactory.get();
    	SqlRunner runner = SqlRunner.create(ds);
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		Connection conn = null;		
		DatabaseMetaData dbmd = null;
		try {
			conn = getConnections(DRIVER,URL,USER,PWD);
			dbmd = conn.getMetaData();
			ResultSet resultSet = dbmd.getTables(null, null, table, new String[] { "TABLE" });
			while (resultSet.next()) {
		    	String tableName=resultSet.getString("TABLE_NAME");
		    	String tableRemarks=resultSet.getString("REMARKS");
		    	if(tableRemarks == null || tableRemarks.equals("")){
		    		tableRemarks = "";
				}
		    	//未指定表保存所有表
		    	if(StrUtil.isEmpty(table)){
		    		saveData(runner, conn, tableName,tableRemarks);
		    	}else{
		    		//保存指定表
		    		if(tableName.equals(table)){
		    			saveData(runner, conn, table,tableRemarks);
			    	}
		    	}
		    }
			System.err.println("保存成功！");
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
	/**
	 * 新增menu
	 * @author linfei
	 * @throws SQLException 
	 */
	public static void insertMenu() throws SQLException{
		List<Entity> description = Db.use().findAll("table_description");
		List<Entity> list = new ArrayList<Entity>();
		Sys_Menu menu = null;
		for (Entity entity : description) {
			menu = new Sys_Menu();
			menu.setCode(entity.getStr("table_name"));
			menu.setName(entity.getStr("table_remarks"));
			menu.setUrl(entity.getStr("url"));
			menu.setIsmenu(1);
			menu.setStatus(1);
			menu.setLevels(2);
			menu.setNum(1);
			menu.setPcode("data_conter");
			menu.setPcodes("[0],[data_conter],");
			list.add(Entity.parse(menu));
		}
		DataSource ds = DSFactory.get();
    	SqlRunner runner = SqlRunner.create(ds);
		runner.insert(list);
	}
	/**
	 * 保存表关系数据
	 * @author linfei
	 * @param runner
	 * @param conn
	 * @param tableName
	 * @param tableRemarks
	 * @throws SQLException
	 * @throws Exception
	 */
	private static void saveData(SqlRunner runner, Connection conn,String tableName,String tableRemarks) throws SQLException, Exception {
		TableDescription tableDescription = new TableDescription();
		tableDescription.setTableName(tableName);
		tableDescription.setTableRemarks(tableRemarks);
		String URL = "/dataCenter/index?db="+tableName;
		tableDescription.setUrl(URL);
		runner.insert(Entity.parseWithUnderlineCase(tableDescription));
		
		ResultSet rs = conn.getMetaData().getColumns(null, getSchema(conn),tableName.toUpperCase(), "%");
		while(rs.next()){
			TableFieldDescription fieldDescription = new TableFieldDescription();
			//System.out.println("字段名："+rs.getString("COLUMN_NAME")+"--字段注释："+rs.getString("REMARKS")+"--字段数据类型："+rs.getString("TYPE_NAME"));
			String colName = rs.getString("COLUMN_NAME");
			String remarks = rs.getString("REMARKS");
			if(remarks == null || remarks.equals("")){
				remarks = "";
			}
			fieldDescription.setFieldRemarks(remarks);
			fieldDescription.setTableName(tableName);
			fieldDescription.setField(colName);
			runner.insert(Entity.parseWithUnderlineCase(fieldDescription));
		}
	}
	/**
	 * 获取连接
	 * @author linfei
	 * @param driver
	 * @param url
	 * @param user
	 * @param pwd
	 * @return
	 * @throws Exception
	 */
	private static Connection getConnections(String driver,String url,String user,String pwd) throws Exception {
		Connection conn = null;
		try {
			Properties props = new Properties();
			props.put("remarksReporting", "true");
			props.put("user", user);
			props.put("password", pwd);
			Class.forName(driver);
			conn = DriverManager.getConnection(url, props);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return conn;
	}
	//其他数据库不需要这个方法 oracle和db2需要
	private static String getSchema(Connection conn) throws Exception {
		String schema;
		schema = conn.getMetaData().getUserName();
		if ((schema == null) || (schema.length() == 0)) {
			throw new Exception("ORACLE数据库模式不允许为空");
		}
		return schema.toUpperCase().toString();
	}
	public static void main(String[] args) {
		//添加数据表信息到中间表
		//DbInfoUtil.addTableInfo("table_description");
		//新增menu
		try {
			insertMenu();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}
